USE [NLKHOnline]
GO

/****** Object:  StoredProcedure [dbo].[SearchChuyenGiaOnline]    Script Date: 3/23/2014 11:18:29 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- 2014-03-22: hien created
-- =============================================

CREATE PROCEDURE [dbo].[SearchChuyenGiaOnline] 
	@startIndex int,
	@pageSize int,
	@sorting varchar(50),
	@hoTen nvarchar(255) = '',
	@tenDonVi nvarchar(255) = '',
	@tenCongTrinh nvarchar(255) = '',
	@linhVucId int = 0,
	@hocHamId int = 0,
	@hocViId int = 0
AS
BEGIN
	select Id, HoTen, TenLinhVuc
	from
	(select CG.Id, CG.HoTen, LV.Ten as TenLinhVuc, 
		ROW_NUMBER() Over (
			order by 
				case when @sorting = 'HoTen ASC' then HoTen end,
				case when @sorting = 'HoTen DESC' then HoTen end DESC) AS RowNumber
	from ChuyenGia CG
		left outer join LinhVuc LV on CG.LinhVucId = LV.Id
	where 
		((@linhVucId = 0) or (LinhVucId = @linhVucId)) and
		((@hocHamId = 0) or (HocHamId = @hocHamId)) and
		((@hocViId = 0) or (HocViId = @hocViId)) and
		((@hoTen = '') or (HoTen like N'%' + @hoTen + '%')) and
		((@tenDonVi = '') or (TenDonVi like N'%' + @tenDonVi + '%')) and
		((@tenCongTrinh = '') or 
			(CG.Id in 
				(select distinct ChuyenGiaId
				from CongTrinhNghienCuu
				where (TenCongTrinh like N'%' + @tenCongTrinh + '%')
				)
			))
	) ChuyenGiaPage
	where RowNumber > @startIndex and RowNumber <= @startIndex + @pageSize
END

GO


